package info;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ShopDbBean {
	
	public List< Shop > getAll(){
	
		ArrayList< Shop > data = new ArrayList< Shop >();
		Connection conn = DbConn.GetConnection();// 取得连接 
		
		try{
	 		
 			PreparedStatement st = conn.prepareStatement( 
 					"select id, name, imageURL, score, sale, sprice, dprice, distance, time"
 		 					+ " from  shop" );
 		 					
			ResultSet rs = st.executeQuery(  );// 执行查询语句
 			
 			System.out.println( "查询结果为：" );
 			
 			while( rs.next() ){
 				
 				Shop info = new Shop();
 				// `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
 			    int id = rs.getInt("id");
 			    info.setId( id );
				// `name` VARCHAR(45) NULL,
				String name = rs.getString( "name" );
				info.setName(name);
				
				String imageURL = rs.getString( "imageURL" );
				info.setImageURL(imageURL);
				
				String score = rs.getString( "score" );
				info.setScore(score);
				
				String sale = rs.getString( "sale" );
				info.setSale(sale);
				
				String sprice = rs.getString( "sprice" );
				info.setSprice(sprice);
				
				String dprice = rs.getString( "dprice" );
				info.setDprice(dprice);
				
				String distance = rs.getString( "distance" );
				info.setDistance(distance);
				
				String time = rs.getString( "time" );
				info.setTime(time);
				
				
 				data.add(info);
 				
 			}
 			
 		}
 		catch( SQLException e ){
 			System.out.printf( "数据库查询失败\n" + e.getMessage()  );
 		}
		finally{
 			if( conn != null ){
 				try{
 					conn.close();
 				}
 				catch( SQLException e ){
 					System.out.printf( "关闭连接失败\n" + e.getMessage()  );
 				}// try
 			}// if
 			
 		}// finally
 		
 		return data;

	}
	
	
	

public List< Shop > search(String sname){
	
	ArrayList< Shop > data = new ArrayList< Shop >();
	Connection conn = DbConn.GetConnection();// 取得连接 
	
	try{
 		
		String sql = "select * from shop where name = ?";
		PreparedStatement st = conn.prepareStatement(  sql );
		st.setString(1, sname);
		
		ResultSet rs = st.executeQuery(  );// 执行查询语句
			
			System.out.println( "查询结果为：" );
			
			while( rs.next() ){
				
				Shop info = new Shop();
				// `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键',
			    int id = rs.getInt("id");
			    info.setId( id );
			// `name` VARCHAR(45) NULL,
			String name = rs.getString( "name" );
			info.setName(name);
			
			String imageURL = rs.getString( "imageURL" );
			info.setImageURL(imageURL);
			
			String score = rs.getString( "score" );
			info.setScore(score);
			
			String sale = rs.getString( "sale" );
			info.setSale(sale);
			
			String sprice = rs.getString( "sprice" );
			info.setSprice(sprice);
			
			String dprice = rs.getString( "dprice" );
			info.setDprice(dprice);
			
			String distance = rs.getString( "distance" );
			info.setDistance(distance);
			
			String time = rs.getString( "time" );
			info.setTime(time);
			
				data.add(info);
				
			}
			
		}
		catch( SQLException e ){
			System.out.printf( "数据库查询失败\n" + e.getMessage()  );
		}
	finally{
			if( conn != null ){
				try{
					conn.close();
				}
				catch( SQLException e ){
					System.out.printf( "关闭连接失败\n" + e.getMessage()  );
				}// try
			}// if
			
		}// finally
		
		return data;

}


}

